﻿using com.kmer.ApiServer;
using com.kmer.ApiServer.Attributes;
using com.kmer.Attributes;
using com.kmer.db;
using com.kmer.Helper;
using kmer.Data;
using OrderAPI.Model;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.kmer.library;
using com.kmer.library.Helper;
using NHibernateVertica;
using Vertica.Data.VerticaClient;
using System.Data;
using EntityFramework.Extensions;

namespace OrderAPI
{
    [Description("审版列表，详情，评分")]
    public class TrialChooseVersionIPadHandler : BaseHandler
    {
        #region 属性
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();
        #endregion

        #region API
        [Description("获取当前正在执行的订货会")]
        [ReturnType(typeof(IList<OrderMaster.LoginOrderMaster>))]
        public ApiResult GetOrderMaster()
        {
            var dc = new km_platformEntities();
            //var result = dc.t_order_master.Where(t=>t.OrderFlag==false 
            //&& t.BeginDate<=DateTime.Now 
            //&& t.EndDate>=DateTime.Now).ToList().Select(t=>new OrderMaster.LoginOrderMaster
            //{
            //    MasterID=t.MasterID,OrderName=t.OrderName
            //}).ToList();

            var result = from a in dc.t_order_master
                         join b in dc.t_order_styledetail
                         on a.MasterID equals b.MasterID
                         where a.OrderFlag == false && a.BeginDate <= DateTime.Now && a.EndDate >= DateTime.Now
                         select new
                         {
                             MasterID = a.MasterID,
                             OrderName = a.OrderName
                         };
            var list = result.GroupBy(q => new { q.MasterID, q.OrderName }).Select(q => new
            {
                MasterID = q.Key.MasterID,
                OrderName = q.Key.OrderName
            }).ToList();

            return Success(list);
        }

        [MustLogin]
        [Description("获取当前订货会的系列，商品层，项目")]
        [ReturnType(typeof(OrderMaster.BaseParamData))]
        public ApiResult GetBaseParam([ParamDescription("订货会ID")]int MasterID)
        {
            if (MasterID == 0)
            {
                return Error("订货会内码ID不能为0");
            }

            var dc = new km_platformEntities();
            var result = new OrderMaster.BaseParamData();
            result.Series = dc.t_order_styledetail
                .Where(t => t.MasterID == MasterID).GroupBy(x => x.SerialName).Select(t => t.Key).ToList();
            result.Goods = dc.t_order_styledetail
              .Where(t => t.MasterID == MasterID).GroupBy(x => x.MaterialProperty).Select(t => t.Key).ToList();
            result.Items = dc.t_order_styledetail
              .Where(t => t.MasterID == MasterID).GroupBy(x => x.ItemName).Select(t => t.Key).ToList();
            return Success(result);
        }

        [MustLogin]
        [Description("获取待审商品列表")] //{"MasterID":49,"Series":"创意","CommodityLayer":"测试1","Item":"长袖线衣","SerialNumber":"C00500","IsScore":"1","Page":1,"Size":20}
        [ReturnType(typeof(PaperData<OrderMaster.OrderStyleDetailData>))]
        public ApiResult GetGoodList(
            [ParamDescription("订货会ID")]int MasterID,
            [ParamDescription("系列")]string Series = "",
            [ParamDescription("商品层")]string CommodityLayer = "",
            [ParamDescription("项目")]string Item = "",
            [ParamDescription("流水号")]string SerialNumber = "",
            [ParamDescription("是否评分：''=全部 '1'=已评 '0'=未评")]string IsScore = "",
            [ParamDescription("页码")]int Page = 1,
            [ParamDescription("页数")]int Size = 20)
        {
            if (MasterID == 0)
            {
                return Error("订货会内码ID不能为0");
            }

            var dc = new km_platformEntities();
            //var query = dc.t_order_styledetail.Where(t => t.MasterID == MasterID && t.CompanyID == this.CompanyID);
            var query = from o in dc.t_order_styledetail
                        join b in dc.t_order_master
                        on o.MasterID equals b.MasterID
                        where o.MasterID == MasterID && o.CompanyID == this.CompanyID
                        select new
                        {
                            o.DetailID,
                            o.MasterID,
                            o.SerialNo,
                            o.DesignNo,
                            o.SerialName,
                            o.ItemName,
                            o.MaterialProperty,
                            o.RetailPrice,
                            b.OrderName
                        };


            if (!string.IsNullOrWhiteSpace(SerialNumber))
                query = query.Where(t => t.SerialNo.Contains(SerialNumber));
            if (!string.IsNullOrWhiteSpace(Series))
                query = query.Where(t => t.SerialName == Series);
            if (!string.IsNullOrWhiteSpace(CommodityLayer))
                query = query.Where(t => t.MaterialProperty == CommodityLayer);
            if (!string.IsNullOrWhiteSpace(Item))
                query = query.Where(t => t.ItemName == Item);

            if (!string.IsNullOrWhiteSpace(IsScore))
            {
                if (IsScore == "1")
                {
                    query = from o in query
                            join b in dc.t_order_GradeResult_Mater
                            on o.SerialNo equals b.SerialNo
                            where b.MasterID == MasterID && b.UserID == this.CusID && b.CompanyID == this.CompanyID
                            select o;
                }
                else
                {
                    query = from o in query
                            where !(from b in dc.t_order_GradeResult_Mater
                                    where b.MasterID == MasterID && b.UserID == this.CusID && b.CompanyID == this.CompanyID
                                    select b.SerialNo).Contains(o.SerialNo)
                            select o;
                }
            }

            var result = PaperData<OrderMaster.OrderStyleDetailData>.Create(query.Count(), Size, Page);
            result.Items = query.OrderBy(t => t.DetailID)
                .Skip(result.Skip)
                .Take(result.Limt).ToList()
                .Select(o => new OrderMaster.OrderStyleDetailData
                {
                    DetailID = o.DetailID,
                    MasterID = o.MasterID,
                    SerialNo = o.SerialNo,
                    DesignNo = o.DesignNo,
                    SerialName = o.SerialName,
                    ItemName = o.ItemName,
                    MaterialProperty = o.MaterialProperty,
                    RetailPrice = o.RetailPrice,
                    ImgUrl = o.SerialNo == null ? "" : Request.GetOSSImageUrl(o.SerialNo, o.OrderName, 1),
                    IsScore = dc.t_order_GradeResult_Mater.FirstOrDefault(t => (t.MasterID == o.MasterID && t.SerialNo == o.SerialNo && t.UserID == this.CusID && t.CompanyID == this.CompanyID)) != null,
                    IsChooseFlag = dc.t_order_ChooseVersion.FirstOrDefault(t => t.MasterID == o.MasterID && t.SerialNo == o.SerialNo) != null
                }).ToList();
            return Success(result);
        }

        /// writer：ljj
        /// date：2018-03-16
        [MustLogin]
        [Description("找相似")]
        [ReturnType(typeof(IList<OrderMaster.OrderStyleDetailData>))]
        public ApiResult FindSimilar([ParamDescription("订货会ID")] int MasterID,
            [ParamDescription("流水号")] string SerialNo, [ParamDescription("系列")] string Series,
            [ParamDescription("项目")] string Item)
        {
            try
            {
                if (MasterID == 0)
                {
                    return Error("订货会内码ID不能为0");
                }
                var dc = new km_platformEntities();
                var list = (from o in dc.t_order_styledetail
                            join b in dc.t_order_master
                            on o.MasterID equals b.MasterID
                            where o.MasterID == MasterID && o.CompanyID == this.CompanyID && o.SerialNo != SerialNo &&
                                  o.SerialName.Contains(Series) && o.ItemName.Contains(Item)
                            select new
                            {
                                o.DetailID,
                                o.MasterID,
                                o.SerialNo,
                                o.DesignNo,
                                o.SerialName,
                                o.ItemName,
                                o.MaterialProperty,
                                o.RetailPrice,
                                b.OrderName
                            }).OrderBy(t => t.DetailID).ToList().Select(o => new OrderMaster.OrderStyleDetailData
                            {
                                DetailID = o.DetailID,
                                MasterID = o.MasterID,
                                SerialNo = o.SerialNo,
                                DesignNo = o.DesignNo,
                                SerialName = o.SerialName,
                                ItemName = o.ItemName,
                                MaterialProperty = o.MaterialProperty,
                                RetailPrice = o.RetailPrice,
                                ImgUrl = o.SerialNo == null ? "" : Request.GetOSSImageUrlV2(o.MasterID, o.SerialNo),
                                IsScore = dc.t_order_GradeResult_Mater.FirstOrDefault(
                                          t => (t.MasterID == o.MasterID && t.SerialNo == o.SerialNo &&
                                                t.UserID == this.CusID &&
                                                t.CompanyID == this.CompanyID)) != null,
                                IsChooseFlag =
                                dc.t_order_ChooseVersion.FirstOrDefault(
                                    t => t.MasterID == o.MasterID && t.SerialNo == o.SerialNo) != null
                            }).ToList();
                return Success(list);
            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }

        /// <summary>
        /// 获取待审商品列表V2 api
        /// 时间：2018-03-16
        /// </summary>
        /// <param name="MasterID">订货会ID</param>
        /// <param name="Series">系列</param>
        /// <param name="CommodityLayer">商品层</param>
        /// <param name="Item">项目</param>
        /// <param name="SerialNumber">流水号</param>
        /// <param name="IsScore">是否评分</param>
        /// <param name="Page">页码</param>
        /// <param name="Size">页数</param>
        /// <returns></returns>
        [MustLogin]
        [Description("获取待审商品列表V2")] //{"MasterID":49,"Series":"创意","CommodityLayer":"测试1","Item":"长袖线衣","SerialNumber":"C00500","IsScore":"1","Page":1,"Size":20}
        [ReturnType(typeof(PaperData<OrderMaster.OrderStyleDetailData>))]
        public ApiResult GetGoodListV2(
            [ParamDescription("订货会ID")]int MasterID,
            [ParamDescription("系列")]string Series = "",
            [ParamDescription("商品层")]string CommodityLayer = "",
            [ParamDescription("项目")]string Item = "",
            [ParamDescription("流水号")]string SerialNumber = "",
            [ParamDescription("是否评分：''=全部 '1'=已评 '0'=未评")]string IsScore = "",
            [ParamDescription("页码")]int Page = 1,
            [ParamDescription("页数")]int Size = 20)
        {
            if (MasterID == 0)
            {
                return Error("订货会内码ID不能为0");
            }

            var dc = new km_platformEntities();
            //var query = dc.t_order_styledetail.Where(t => t.MasterID == MasterID && t.CompanyID == this.CompanyID);
            var query = from o in dc.t_order_styledetail
                        join b in dc.t_order_master
                        on o.MasterID equals b.MasterID
                        where o.MasterID == MasterID && o.CompanyID == this.CompanyID
                        select new
                        {
                            o.DetailID,
                            o.MasterID,
                            o.SerialNo,
                            o.DesignNo,
                            o.SerialName,
                            o.ItemName,
                            o.MaterialProperty,
                            o.RetailPrice,
                            b.OrderName
                        };


            if (!string.IsNullOrWhiteSpace(SerialNumber))
                query = query.Where(t => t.SerialNo.Contains(SerialNumber));
            if (!string.IsNullOrWhiteSpace(Series))
                query = query.Where(t => t.SerialName == Series);
            if (!string.IsNullOrWhiteSpace(CommodityLayer))
                query = query.Where(t => t.MaterialProperty == CommodityLayer);
            if (!string.IsNullOrWhiteSpace(Item))
                query = query.Where(t => t.ItemName == Item);

            if (!string.IsNullOrWhiteSpace(IsScore))
            {
                if (IsScore == "1")
                {
                    query = from o in query
                            join b in dc.t_order_GradeResult_Mater
                            on o.SerialNo equals b.SerialNo
                            where b.MasterID == MasterID && b.UserID == this.CusID && b.CompanyID == this.CompanyID
                            select o;
                }
                else
                {
                    query = from o in query
                            where !(from b in dc.t_order_GradeResult_Mater
                                    where b.MasterID == MasterID && b.UserID == this.CusID && b.CompanyID == this.CompanyID
                                    select b.SerialNo).Contains(o.SerialNo)
                            select o;
                }
            }

            var result = PaperData<OrderMaster.OrderStyleDetailData>.Create(query.Count(), Size, Page);
            result.Items = query.OrderBy(t => t.DetailID)
                .Skip(result.Skip)
                .Take(result.Limt).ToList()
                .Select(o => new OrderMaster.OrderStyleDetailData
                {
                    DetailID = o.DetailID,
                    MasterID = o.MasterID,
                    SerialNo = o.SerialNo,
                    DesignNo = o.DesignNo,
                    SerialName = o.SerialName,
                    ItemName = o.ItemName,
                    MaterialProperty = o.MaterialProperty,
                    RetailPrice = o.RetailPrice,
                    ImgUrl = "http://kmer-online-pic.oss-cn-shenzhen.aliyuncs.com/Inspection/20180813/636697524023892889.png",//o.SerialNo == null ? "" : Request.GetOSSImageUrlV2(o.MasterID, o.SerialNo),
                    IsScore = dc.t_order_GradeResult_Mater.Where(t => (t.MasterID == o.MasterID && t.SerialNo == o.SerialNo && t.UserID == this.CusID && t.CompanyID == this.CompanyID)).FirstOrDefault() == null ? false : true,
                    IsChooseFlag = dc.t_order_ChooseVersion.Where(t => t.MasterID == o.MasterID && t.SerialNo == o.SerialNo).FirstOrDefault() == null ? false : true
                }).ToList();
            return Success(result);
        }

        [MustLogin]
        [Description("获取订货会的年份，季节")]
        [ReturnType(typeof(PaperData<OrderMaster.YearSeasonData>))]
        public ApiResult GetYearSeason()
        {
            try
            {
                var sql = $@"SELECT dictName,parentDictValue 
FROM ndw.ndw_global_dictionary
where CompanyID = '{this.CompanyID}' and (parentDictValue='年份' or parentDictValue='季节') and isValid = 0
order by dictName";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                dt.TableName = "YearSeason";
                DataView view = new DataView();
                view.Table = dt;
                view.RowFilter = "parentDictValue = '年份'";
                var Year = view.ToTable();
                view.RowFilter = "parentDictValue = '季节'";
                var Season = view.ToTable();

                var result = new OrderMaster.YearSeasonData();
                result.Year = ModelConvertHelper<OrderMaster.YearSeasonName>.ConvertToModel(Year);
                result.Season = ModelConvertHelper<OrderMaster.YearSeasonName>.ConvertToModel(Season);

                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }

        [MustLogin]
        [Description("获取待审商品详情")]
        [ReturnType(typeof(PaperData<OrderMaster.OrderStyleDetailScoreList>))] //{"MasterID":49,"DesignNo":"G81221","Grade":"goods","Word":"entry","Score":"score"}
        public ApiResult GetGoodDetail([ParamDescription("订货会内码ID")]int MasterID, [ParamDescription("设计号")]string DesignNo, [ParamDescription("商品评分项标识")]string Grade = "goods", [ParamDescription("常用词项标识")]string Word = "entry", [ParamDescription("分数值项标识")]string Score = "score")
        {

            try
            {
                if (MasterID == 0)
                {
                    return Error("订货会内码ID不能为0");
                }
                if (string.IsNullOrWhiteSpace(DesignNo))
                {
                    return Error("设计号不能为空");
                }

                var dc = new km_platformEntities();
                var result = new OrderMaster.OrderStyleDetailScoreList();
                //TODO:1、商品相关;
                var CompanyID = this.CompanyID;
                //var queryData = dc.t_order_styledetail.Where(t => (t.MasterID == MasterID && t.DesignNo == DesignNo && t.CompanyID == CompanyID)).ToList();
                var queryData = (from o in dc.t_order_styledetail
                                 join b in dc.t_order_master
                                 on o.MasterID equals b.MasterID
                                 where o.MasterID == MasterID && o.DesignNo == DesignNo && o.CompanyID == this.CompanyID
                                 select new
                                 {
                                     o.DetailID,
                                     o.MasterID,
                                     o.SerialNo,
                                     o.DesignNo,
                                     o.ColorName,
                                     o.YearNo,
                                     o.SeasonName,
                                     o.SerialName,
                                     o.MaterialProperty,
                                     o.ItemName,
                                     o.ModelName,
                                     o.ThicknessName,
                                     o.ElementName,
                                     o.CollarTypeName,
                                     o.ClothesVersionName,
                                     o.CommodityProfileName,
                                     o.DesignName,
                                     o.StuffName,
                                     o.Lining,
                                     o.VendCustName,
                                     o.RetailPrice,
                                     o.CostPrice,
                                     o.OrderRate,
                                     b.OrderName
                                 }).ToList();

                result.ScoreData = queryData.OrderBy(t => t.DetailID).Select(
                    o => new OrderMaster.OrderStyleDetailScoreData
                    {
                        DetailID = o.DetailID,
                        MasterID = o.MasterID,
                        SerialNo = o.SerialNo,
                        DesignNo = o.DesignNo,
                        ColorName = o.ColorName,
                        YearNo = o.YearNo,
                        SeasonName = o.SeasonName,
                        SerialName = o.SerialName,
                        MaterialProperty = o.MaterialProperty,
                        ItemName = o.ItemName,
                        ModelName = o.ModelName,
                        ThicknessName = o.ThicknessName,
                        ElementName = o.ElementName,
                        CollarTypeName = o.CollarTypeName,
                        ClothesVersionName = o.ClothesVersionName,
                        CommodityProfileName = o.CommodityProfileName,
                        DesignName = o.DesignName,
                        StuffName = o.StuffName,
                        Lining = o.Lining,
                        VendCustName = o.VendCustName,
                        RetailPrice = o.RetailPrice,
                        CostPrice = o.CostPrice,
                        OrderRate = o.OrderRate,
                        ImgUrl = o.SerialNo == null ? "" : Request.GetOSSImageUrlV2(o.MasterID, o.SerialNo),

                        IsScore = dc.t_order_GradeResult_Mater
                                      .Where(t => (t.MasterID == o.MasterID && t.SerialNo == o.SerialNo &&
                                                   t.UserID == this.CusID && t.CompanyID == CompanyID))
                                      .FirstOrDefault() == null
                            ? false
                            : true,
                        Remark = GetRemark(o.MasterID, o.SerialNo),
                        IsChooseFlag = dc.t_order_ChooseVersion
                                           .Where(t => t.MasterID == o.MasterID && t.SerialNo == o.SerialNo)
                                           .FirstOrDefault() == null
                            ? false
                            : true,

                        //ScoreItem = GetGrade(o.MasterID, o.SerialNo),
                        GradeWordItem = GradeWordItem(Grade, Word, Score),

                        CommodityScore = GetCommodityScore(o.MasterID, o.SerialNo),

                        CountNum = GetCountNum(o.MasterID, o.SerialName, o.ItemName, o.SerialNo)

                    }).ToList();

                #region 报文调整到一个数组中
                ////TODO:2、评分项相关;
                //var queryItem = dc.t_order_GradeDetail.Where(t => (t.MasterID == MasterID && t.GradeRate > 0));
                //result.ScoreItem = queryItem.Select(o => new OrderMaster.OrderGradeDetailData
                //{
                //    GradeID = o.GradeID,
                //    GradeName = o.GradeName,
                //    GradeRate = o.GradeRate
                //}).ToList();

                ////TODO:3、评分项数据相关;
                //result.ScoreGrade = new List<OrderMaster.GetOrderGradeResultList>();
                //foreach (var item in result.ScoreData)
                //{
                //    var model = GetGrade(item.MasterID,item.SerialNo);
                //    if (model == null)
                //    {
                //        model = new OrderMaster.GetOrderGradeResultList();
                //        model.ResultID = 0;
                //        model.PersonalScore = 0;
                //        model.ScoreData = new List<OrderMaster.OrderGradeResultDetailData>();
                //        foreach (var item2 in result.ScoreItem)
                //        {
                //            var model2 = new OrderMaster.OrderGradeResultDetailData();
                //            model2.GradeID = item2.GradeID;
                //            model2.Score = 0;
                //            model.ScoreData.Add(model2);
                //        }
                //    }
                //    result.ScoreGrade.Add(model);
                //}
                #endregion

                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }

        [MustLogin]
        [Description("获取当前订货会下所有的 设计号")]
        [ReturnType(typeof(OrderMaster.DesignNoData))] //{"MasterID":38}
        public ApiResult GetDesignNoList([ParamDescription("订货会内码ID")]int MasterID)
        {
            try
            {
                if (MasterID == 0)
                {
                    return Error("订货会内码ID不能为0");
                }

                var dc = new km_platformEntities();
                var result = new OrderMaster.DesignNoData();
                var query = from o in dc.t_order_styledetail where o.MasterID == MasterID && o.CompanyID == this.CompanyID select new { DesignNo = o.DesignNo };
                result.DesignNo = query.GroupBy(x => x.DesignNo).Select(t => t.Key).ToList();
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }

        [Description("保存评分数据")]
        [MustLogin]
        [JsonType]
        [ReturnType(typeof(int), "0-成功")] //{"ResultID":0,"MasterID":38,"SerialNo":"C00500","PersonalScore":8.1,"ScoreData":[{"GradeID":224,"Score":8.1},{"GradeID":225,"Score":8.1},{"GradeID":226,"Score":8.1},{"GradeID":227,"Score":8.1},{"GradeID":228,"Score":8.1},{"GradeID":229,"Score":8.1},{"GradeID":230,"Score":8.1}]}
        public ApiResult SaveGrade([ParamDescription("参数实体类")] OrderMaster.SaveOrderGradeResult param)
        {
            try
            {
                if (param == null || param.ScoreData.Count == 0) return Error("参数行不能为空");
                if (param.MasterID == 0)
                {
                    return Error("订货会内码ID不能为0");
                }
                if (string.IsNullOrEmpty(param.SerialNo))
                {
                    return Error("流水号不能为空");
                }
                if (this.CusID == 0)
                {
                    return Error("账号登入已过期请求重新登入");
                }

                //var dc = new km_platformEntities();
                using (var dc = new km_platformEntities())
                {
                    var bol = dc.t_order_ChooseVersion.FirstOrDefault(t => t.MasterID == param.MasterID && t.SerialNo == param.SerialNo && t.CompanyID == this.CompanyID);
                    if (bol == null)
                    {
                        //dc.t_order_GradeResult_Detail.Where(t => t.ResultID == param.ResultID).Delete();
                        //dc.t_order_GradeResult_Mater.Where(t => t.ResultID == param.ResultID).Delete();
                        if (param.ResultID == 0)
                        {
                            var tran = dc.Database.BeginTransaction();  //开启事务
                            try
                            {
                                var query = dc.t_order_GradeResult_Mater.Where(t => t.MasterID == param.MasterID && t.SerialNo == param.SerialNo && t.UserID == this.CusID && t.CompanyID == this.CompanyID).ToList();
                                if (query.Count > 0)
                                {
                                    return Error("ResultID不能为空");
                                    //return Success(query.FirstOrDefault().ResultID);
                                }

                                var data = new t_order_GradeResult_Mater
                                {
                                    MasterID = param.MasterID,
                                    UserID = this.CusID,
                                    CompanyID = this.CompanyID,
                                    ExtrSource = this.CompanyID,
                                    SerialNo = param.SerialNo,
                                    GradeTime = DateTime.Now,
                                    PersonalScore = param.PersonalScore,
                                    Remark = param.Remark,
                                };
                                dc.t_order_GradeResult_Mater.Add(data);
                                dc.SaveChanges();
                                var data2Id = data.ResultID;

                                foreach (var item in param.ScoreData)
                                {
                                    var data2 = new t_order_GradeResult_Detail
                                    {
                                        ResultID = data2Id,
                                        Score = item.Score,
                                        GradeID = item.GradeID
                                    };
                                    dc.t_order_GradeResult_Detail.Add(data2);
                                }
                                dc.SaveChanges();
                                tran.Commit();  //必须调用Commit()，不然数据不会保存
                                return Success(data.ResultID);
                            }
                            catch (Exception ex)
                            {
                                tran.Rollback();    //出错就回滚
                                return Error(ex);
                            }

                        }
                        else
                        {
                            var tran2 = dc.Database.BeginTransaction();  //开启事务
                            try
                            {
                                var query = dc.t_order_GradeResult_Mater.FirstOrDefault(t => t.ResultID == param.ResultID);
                                if (query != null)
                                {
                                    query.MasterID = param.MasterID;
                                    query.UserID = this.CusID;
                                    query.CompanyID = this.CompanyID;
                                    query.ExtrSource = this.CompanyID;
                                    query.SerialNo = param.SerialNo;
                                    query.GradeTime = DateTime.Now;
                                    query.PersonalScore = param.PersonalScore;
                                    query.Remark = param.Remark;
                                    dc.SaveChanges();

                                    foreach (var item in param.ScoreData)
                                    {
                                        var query2 = dc.t_order_GradeResult_Detail.FirstOrDefault(t => (t.ResultID == param.ResultID && t.GradeID == item.GradeID));
                                        if (query2 != null)
                                        {
                                            query2.ResultID = param.ResultID;
                                            query2.GradeID = item.GradeID;
                                            query2.Score = item.Score;
                                            dc.SaveChanges();
                                        }
                                    }
                                }
                                tran2.Commit();  //必须调用Commit()，不然数据不会保存
                                return Success(param.ResultID);
                            }
                            catch (Exception ex)
                            {
                                tran2.Rollback();    //出错就回滚
                                return Error(ex);
                            }
                        }
                    }
                    else
                    {
                        return Error("已定版不可修改");
                    }
                }
            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }

        /// writer：ljj
        /// date：2018-03-19
        [Description("保存评分数据")]
        [MustLogin]
        [JsonType]
        [ReturnType(typeof(int), "0-成功")] //{"ResultID":0,"MasterID":38,"SerialNo":"C00500","PersonalScore":8.1,"ScoreData":[{"GradeID":224,"Score":8.1},{"GradeID":225,"Score":8.1},{"GradeID":226,"Score":8.1},{"GradeID":227,"Score":8.1},{"GradeID":228,"Score":8.1},{"GradeID":229,"Score":8.1},{"GradeID":230,"Score":8.1}]}
        public ApiResult SaveGradeV2([ParamDescription("参数实体类")] OrderMaster.SaveOrderGradeResultV2 param)
        {
            try
            {
                if (param?.ScoreData == null) return Error("参数行不能为空");
                if (param.MasterID == 0)
                {
                    return Error("订货会内码ID不能为0");
                }
                if (string.IsNullOrEmpty(param.SerialNo))
                {
                    return Error("流水号不能为空");
                }
                if (this.CusID == 0)
                {
                    return Error("账号登入已过期请求重新登入");
                }

                //var dc = new km_platformEntities();
                using (var dc = new km_platformEntities())
                {
                    var bol = dc.t_order_ChooseVersion.FirstOrDefault(t => t.MasterID == param.MasterID && t.SerialNo == param.SerialNo && t.CompanyID == this.CompanyID);
                    if (bol == null)
                    {
                        //dc.t_order_GradeResult_Detail.Where(t => t.ResultID == param.ResultID).Delete();
                        //dc.t_order_GradeResult_Mater.Where(t => t.ResultID == param.ResultID).Delete();
                        if (param.ResultID == 0)
                        {
                            var tran = dc.Database.BeginTransaction();  //开启事务
                            try
                            {
                                var query = dc.t_order_GradeResult_Mater.Where(t => t.MasterID == param.MasterID && t.SerialNo == param.SerialNo && t.UserID == this.CusID && t.CompanyID == this.CompanyID).ToList();
                                if (query.Count > 0)
                                {
                                    return Error("ResultID不能为空");
                                    //return Success(query.FirstOrDefault().ResultID);
                                }

                                var data = new t_order_GradeResult_Mater
                                {
                                    MasterID = param.MasterID,
                                    UserID = this.CusID,
                                    CompanyID = this.CompanyID,
                                    ExtrSource = this.CompanyID,
                                    SerialNo = param.SerialNo,
                                    GradeTime = DateTime.Now,
                                    PersonalScore = param.PersonalScore,
                                    Remark = param.Remark,
                                };
                                dc.t_order_GradeResult_Mater.Add(data);
                                dc.SaveChanges();
                                var data2Id = data.ResultID;
                                var data2 = new t_order_GradeResult_Detail
                                {
                                    ResultID = data2Id,
                                    AStoreRating = param.ScoreData.AStoreRating,
                                    BStoreRating = param.ScoreData.BStoreRating,
                                    StyleRating = param.ScoreData.StyleRating,
                                };
                                dc.t_order_GradeResult_Detail.Add(data2);
                                dc.SaveChanges();
                                tran.Commit();  //必须调用Commit()，不然数据不会保存
                                return Success(data.ResultID);
                            }
                            catch (Exception ex)
                            {
                                tran.Rollback();    //出错就回滚
                                return Error(ex);
                            }

                        }
                        else
                        {
                            var tran2 = dc.Database.BeginTransaction();  //开启事务
                            try
                            {
                                var query = dc.t_order_GradeResult_Mater.FirstOrDefault(t => t.ResultID == param.ResultID);
                                if (query != null)
                                {
                                    query.MasterID = param.MasterID;
                                    query.UserID = this.CusID;
                                    query.CompanyID = this.CompanyID;
                                    query.ExtrSource = this.CompanyID;
                                    query.SerialNo = param.SerialNo;
                                    query.GradeTime = DateTime.Now;
                                    query.PersonalScore = param.PersonalScore;
                                    query.Remark = param.Remark;
                                    dc.SaveChanges();
                                    var query2 = dc.t_order_GradeResult_Detail.FirstOrDefault(t => t.ResultID == param.ResultID);
                                    if (query2 != null)
                                    {
                                        query2.AStoreRating = param.ScoreData.AStoreRating;
                                        query2.BStoreRating = param.ScoreData.BStoreRating;
                                        query2.StyleRating = param.ScoreData.StyleRating;
                                        dc.SaveChanges();
                                    }
                                }
                                tran2.Commit();  //必须调用Commit()，不然数据不会保存
                                return Success(param.ResultID);
                            }
                            catch (Exception ex)
                            {
                                tran2.Rollback();    //出错就回滚
                                return Error(ex);
                            }
                        }
                    }
                    else
                    {
                        return Error("已定版不可修改");
                    }
                }
            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }

        [MustLogin]
        [Description("获取Vertica年份，季节，系列，商品层，项目")]
        [ReturnType(typeof(PaperData<OrderMaster.SerialMaterialItemData>))]
        public ApiResult GetSerialMaterialItem()
        {
            try
            {
                var sql = $@"SELECT dictName AS text,dictName AS value,0 AS MatConFlag  
FROM ndw.ndw_global_dictionary
WHERE CompanyID = '{this.CompanyID}' AND parentDictValue='年份' AND isValid = 0
UNION ALL
SELECT SeasonName AS text, SeasonID AS value,1 AS MatConFlag 
FROM nods.nods_erp_SD_Mat_Season 
WHERE ExtrSource='{this.CompanyID}'
UNION ALL
SELECT ConName AS text,ConID AS value,MatConFlag
FROM ngoods.ngoods_PL_PlanMatConSet
WHERE 1=1 
AND (MatConFlag = 2 OR MatConFlag = 3) 
AND AllowUsed = 0
AND CompanyID='{this.CompanyID}' 
AND ExtrSource='{this.CompanyID}'
UNION ALL  
SELECT a.ItemName AS text,a.ItemID AS value, 4 AS MatConFlag
FROM ngoods.ngoods_PL_ModelItem a
INNER JOIN ngoods.ngoods_PL_PlanMatConSet b
ON a.ModelID = b.ConID AND b.MatConFlag = 4 
WHERE 1=1 
AND a.ExtrSource = '{this.CompanyID}'
AND b.CompanyID='{this.CompanyID}' 
AND b.ExtrSource='{this.CompanyID}'
ORDER BY value";

                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                dt.TableName = "SerialMaterialItem";
                DataView view = new DataView();
                view.Table = dt;
                view.RowFilter = "MatConFlag = 0 ";
                var Year = view.ToTable();
                view.RowFilter = "MatConFlag = 1 ";
                var Season = view.ToTable();
                view.RowFilter = "MatConFlag = 2 ";
                var SerialName = view.ToTable();
                view.RowFilter = "MatConFlag = 3 ";
                var MaterialProperty = view.ToTable();
                view.RowFilter = "MatConFlag = 4 ";
                var ItemName = view.ToTable();

                var result = new OrderMaster.SerialMaterialItemData();
                result.Year = ModelConvertHelper<OrderMaster.SerialMaterialItemModel>.ConvertToModel(Year);
                result.Season = ModelConvertHelper<OrderMaster.SerialMaterialItemModel>.ConvertToModel(Season);
                result.SerialName = ModelConvertHelper<OrderMaster.SerialMaterialItemModel>.ConvertToModel(SerialName);
                result.MaterialProperty = ModelConvertHelper<OrderMaster.SerialMaterialItemModel>.ConvertToModel(MaterialProperty);
                result.ItemName = ModelConvertHelper<OrderMaster.SerialMaterialItemModel>.ConvertToModel(ItemName);

                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }

        [MustLogin]
        [Description("获取历史结构查询列表")] //{"YearNo":2017,"SeaSonID":"000001","SeriesID":"000001","ItemID":"000029","BeginDate":"2016-10-01","EndDate":"2016-12-31","Page":1,"Size":20}
        [ReturnType(typeof(PaperData<OrderMaster.HistoricalStructureData>))]
        public ApiResult GetHistoricalStructure(
            [ParamDescription("年份")]int YearNo,
            [ParamDescription("季节")]string SeaSonID,
            [ParamDescription("开始日期")]string BeginDate,
            [ParamDescription("结束日期")]string EndDate,
            [ParamDescription("系列")]string SeriesID = "",
            [ParamDescription("项目")]string ItemID = "",
            [ParamDescription("页码")]int Page = 1,
            [ParamDescription("页数")]int Size = 10)
        {
            Random ran = new Random();
            int RandKey = ran.Next(1000, 9999); //创建临时表的随机数
            var tempTableName = $@"ntmp.ntmp_orderPur_{RandKey}_{this.CusID}";
            try
            {
                var whereSql = string.Empty;
                if (!string.IsNullOrWhiteSpace(SeriesID))
                {
                    whereSql += string.Format(@" AND A.SeriesID = '{0}' ", SeriesID);
                }
                if (!string.IsNullOrWhiteSpace(ItemID))
                {
                    whereSql += string.Format(@" AND A.ItemID = '{0}' ", ItemID);
                }

                var createTableSql = string.Format(@"
CREATE TABLE {0} 
AS 
SELECT A.CompanyID,A.YearNo,A.SeaSonID,A.SeriesID,A.ItemID,sum(A.SaleQty) AS SaleQty,sum(A.SaleAmount) AS SaleAmount,sum(A.RetailAmount) AS RetailAmount
FROM ngoods.ngoods_Order_HostPeriodSale AS A 
WHERE 1=1 
AND A.CompanyID='{1}' 
AND A.YearNo={2} 
AND A.SeaSonID='{3}' 
AND A.SaleDate>='{4}' 
AND A.SaleDate<='{5}' 
{6}
GROUP BY A.CompanyID,A.YearNo,A.SeaSonID,A.SeriesID,A.ItemID", tempTableName, this.CompanyID, YearNo, SeaSonID, BeginDate, EndDate, whereSql);

                Helper.DropTempTable(tempTableName);
                var createTableCmd = Helper.GetSqlStringCommand(createTableSql);
                var returnNum = Helper.ExecuteNonQuery(createTableCmd);
                #region 注释
                //var cmdList = new List<CommandInfo>()
                //{
                //    new CommandInfo
                //    {
                //        CommandText =createTableSql,
                //    },
                //};
                //var returnNum = Helper.ExecuteSqlTran(cmdList);
                #endregion

                var countSql = string.Format(@"SELECT COUNT(1) 
FROM ngoods.ngoods_Order_HostPurRate AS A 
LEFT JOIN {0} AS B 
ON A.CompanyID=B.CompanyID AND A.YearNo=B.YearNo AND A.SeaSonID=B.SeaSonID AND A.SeriesID=B.SeriesID AND A.ItemID=B.ItemID
LEFT JOIN 
(
	SELECT CompanyID,YearNo,SeaSonID,SeriesID,sum(SaleAmount) SaleAmount
	FROM {0} 
	GROUP BY CompanyID,YearNo,SeaSonID,SeriesID
) AS C 
ON A.CompanyID=C.CompanyID AND A.YearNo=C.YearNo AND A.SeaSonID=C.SeaSonID AND A.SeriesID=C.SeriesID
LEFT JOIN 
(
	SELECT a.CompanyID,b.YearNo,b.SeaSonID,b.SeriesID,b.ItemID,sum(isnull(qty,0)) qty
	FROM nods.nods_erp_TmpCalcStock A
	INNER JOIN nods.nods_erp_SD_Mat_Material2 b 
	ON a.CompanyID=b.CompanyID AND a.materialid=b.materialid
	WHERE a.CompanyID='{1}' AND a.stockdate='{2}' 
	GROUP BY a.CompanyID,b.YearNo,b.SeaSonID,b.SeriesID,b.ItemID
) AS D
ON a.CompanyID=d.CompanyID AND a.YearNo=d.YearNo  AND a.SeaSonID=d.SeaSonID  AND a.SeriesID=d.SeriesID AND a.ItemID=d.ItemID
LEFT JOIN ngoods.ngoods_PL_ModelItem AS E 
ON A.ItemID=E.ItemID AND A.ExtrSource=E.ExtrSource
LEFT JOIN ngoods.ngoods_PL_PlanMatConSet AS F
ON A.SeriesID=F.ConID AND A.CompanyID=F.CompanyID AND F.MatConFlag = 2
WHERE 1=1 
AND A.CompanyID='{1}' 
AND A.YearNo='{3}'
AND A.SeaSonID='{4}' 
{5} 
", tempTableName, this.CompanyID, EndDate, YearNo, SeaSonID, whereSql);
                var countCmd = Helper.GetSqlStringCommand(countSql);
                var obj = Helper.ExecuteScalar(countCmd);
                var count = 0;
                if (obj != null)
                {
                    count = Convert.ToInt32(obj);
                }

                var sql = string.Format(@"SELECT * FROM (
SELECT 
	ROW_NUMBER()OVER(ORDER BY F.ConName,A.ItemID,A.PurRate ) AS Num,
	F.ConName AS SerialName,
	E.ItemName,
	ROUND(A.PurRate*100,2) AS PurRate,
	ROUND((B.SaleAmount/C.SaleAmount)*100,2) AS PeriodSale,
	ROUND(B.SaleQty/(B.SaleQty+D.qty),2) AS SoldOutRate,
	ROUND((B.SaleAmount/B.retailamount),2) AS Discount 
FROM ngoods.ngoods_Order_HostPurRate AS A 
LEFT JOIN {0} AS B 
ON A.CompanyID=B.CompanyID AND A.YearNo=B.YearNo AND A.SeaSonID=B.SeaSonID AND A.SeriesID=B.SeriesID AND A.ItemID=B.ItemID
LEFT JOIN 
(
	SELECT CompanyID,YearNo,SeaSonID,SeriesID,sum(SaleAmount) SaleAmount
	FROM {0} 
	GROUP BY CompanyID,YearNo,SeaSonID,SeriesID
) AS C 
ON A.CompanyID=C.CompanyID AND A.YearNo=C.YearNo AND A.SeaSonID=C.SeaSonID AND A.SeriesID=C.SeriesID
LEFT JOIN 
(
	SELECT a.CompanyID,b.YearNo,b.SeaSonID,b.SeriesID,b.ItemID,sum(isnull(qty,0)) qty
	FROM nods.nods_erp_TmpCalcStock A
	INNER JOIN nods.nods_erp_SD_Mat_Material2 b 
	ON a.CompanyID=b.CompanyID AND a.materialid=b.materialid
	WHERE a.CompanyID='{1}' AND a.stockdate='{2}' 
	GROUP BY a.CompanyID,b.YearNo,b.SeaSonID,b.SeriesID,b.ItemID
) AS D
ON a.CompanyID=d.CompanyID AND a.YearNo=d.YearNo  AND a.SeaSonID=d.SeaSonID  AND a.SeriesID=d.SeriesID AND a.ItemID=d.ItemID
LEFT JOIN ngoods.ngoods_PL_ModelItem AS E 
ON A.ItemID=E.ItemID AND A.ExtrSource=E.ExtrSource
LEFT JOIN ngoods.ngoods_PL_PlanMatConSet AS F
ON A.SeriesID=F.ConID AND A.CompanyID=F.CompanyID AND F.MatConFlag = 2
WHERE 1=1 
AND A.CompanyID='{1}' 
AND A.YearNo='{3}'
AND A.SeaSonID='{4}' 
{5} 
) table1 WHERE Num BETWEEN ({6}-1)*{7}+1 AND {6}*{7}", tempTableName, this.CompanyID, EndDate, YearNo, SeaSonID, whereSql, Page, Size);
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var result = PaperData<OrderMaster.HistoricalStructureData>.Create(count, Size, Page);
                result.Items = ModelConvertHelper<OrderMaster.HistoricalStructureData>.ConvertToModel(dt);

                return Success(result);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                Helper.DropTempTable(tempTableName);
            }
        }

        [MustLogin]
        [Description("获取历史单款分析列表")] //{"YearNo":2017,"SeaSonID":"000001","CZPFlag":"畅","Num":5,"SeriesID":"000001","MaterialProperty":"","ItemID":"","Page":1,"Size":10}
        [ReturnType(typeof(PaperData<OrderMaster.HistoricalSingleAnalysisData>))]
        public ApiResult GetHistoricalSingleAnalysis(
            [ParamDescription("年份")]int YearNo,
            [ParamDescription("季节")]string SeaSonID,
            [ParamDescription("畅滞")]string CZPFlag,
            [ParamDescription("总条数")]int Num = 5,
            [ParamDescription("系列")]string SeriesID = "",
            [ParamDescription("商品层")]string MaterialProperty = "",
            [ParamDescription("项目")]string ItemID = "",
            [ParamDescription("页码")]int Page = 1,
            [ParamDescription("页数")]int Size = 10)
        {
            try
            {
                string whereSql = string.Format(@" AND OHMA.ExtrSource = '{0}' AND OHMA.CompanyID = '{1}' AND OHMA.YearNo = {2} AND OHMA.SeaSonID = '{3}' ",
                    this.CompanyID, this.CompanyID, YearNo, SeaSonID); //AND OHMA.CZPFlag = '{4}' 
                if (!string.IsNullOrWhiteSpace(SeriesID))
                {
                    whereSql += string.Format(@" AND OHMA.SeriesID = '{0}' ", SeriesID);
                }
                if (!string.IsNullOrWhiteSpace(MaterialProperty))
                {
                    whereSql += string.Format(@" AND OHMA.MaterialProperty = '{0}' ", MaterialProperty);
                }
                if (!string.IsNullOrWhiteSpace(ItemID))
                {
                    whereSql += string.Format(@" AND OHMA.ItemID = '{0}' ", ItemID);
                }
                var countSql = string.Format(@"SELECT ExtrSource FROM ngoods.ngoods_Order_HostMatAn AS OHMA WHERE 1=1 {0} LIMIT {1} ", whereSql, Num);
                var countCmd = Helper.GetSqlStringCommand(countSql);
                var count = Helper.ExecuteDataTable(countCmd).Rows.Count;
                var orderBy = string.Empty;
                if (CZPFlag == "畅")
                {
                    orderBy = "DESC";
                }

                var sql = string.Format(@"SELECT * FROM (
	SELECT 
		ROW_NUMBER()OVER(ORDER BY OHMA.LifeSaleRate {5} ) AS Num,
		OHMA.MaterialID AS MinImage,
		PMCS.ConName AS SerialName,
		MI.ItemName,
		OHMA.MaterialID,
		OHMA.MaterialShortName,
		OHMA.ColorName,
		OHMA.RetailPrice,
		OHMA.SaleQty,
		ROUND(OHMA.Discount,2) AS Discount,
		ROUND(OHMA.LifeSaleRate,2) AS LifeSaleRate,
		OHMA.CZPFlag
	FROM ngoods.ngoods_Order_HostMatAn AS OHMA
	LEFT JOIN ngoods.ngoods_PL_PlanMatConSet AS PMCS
	ON OHMA.SeriesID=PMCS.ConID AND OHMA.CompanyID=PMCS.CompanyID AND PMCS.MatConFlag = 2 
	LEFT JOIN ngoods.ngoods_PL_ModelItem AS MI
	ON OHMA.ItemID=MI.ItemID AND OHMA.ExtrSource=MI.ExtrSource 
	WHERE 1=1 
	{0}
) table1 WHERE Num BETWEEN ({1}-1)*{2}+1 AND {3}*{4} LIMIT {6}", whereSql, Page, Size, Page, Size, orderBy, Num);
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var result = PaperData<OrderMaster.HistoricalSingleAnalysisData>.Create(count, Size, Page);
                var list = ModelConvertHelper<OrderMaster.HistoricalSingleAnalysisData>.ConvertToModel(dt);
                var listItems = list.Select(o => new OrderMaster.HistoricalSingleAnalysisData
                {
                    Num = o.Num,
                    MinImage = Request.GetOSSImageUrl(o.MinImage, "", 2),
                    SerialName = o.SerialName,
                    ItemName = o.ItemName,
                    MaterialID = o.MaterialID,
                    MaterialShortName = o.MaterialShortName,
                    ColorName = o.ColorName,
                    RetailPrice = o.RetailPrice,
                    SaleQty = o.SaleQty,
                    Discount = o.Discount,
                    LifeSaleRate = o.LifeSaleRate,
                    CZPFlag = o.CZPFlag
                }).ToList();
                result.Items = listItems;

                return Success(result);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        [MustLogin]
        [Description("获取本季订单查询列表")] //{"YearNo":2017,"SeaSonID":"000001","SeriesID":"000001","MaterialProperty":"","ItemID":"","Page":1,"Size":10}
        [ReturnType(typeof(PaperData<OrderMaster.SeasonOrdersData>))]
        public ApiResult GetSeasonOrders(
            [ParamDescription("年份")]int YearNo,
            [ParamDescription("季节")]string SeaSonID,
            [ParamDescription("系列")]string SeriesID = "",
            [ParamDescription("商品层")]string MaterialProperty = "",
            [ParamDescription("项目")]string ItemID = "",
            [ParamDescription("页码")]int Page = 1,
            [ParamDescription("页数")]int Size = 10)
        {
            try
            {
                string whereSql = string.Format(@" AND OHMA.ExtrSource = '{0}' AND OHMA.CompanyID = '{1}' AND OHMA.YearNo = {2} AND OHMA.SeaSonID = '{3}' ",
                    this.CompanyID, this.CompanyID, YearNo, SeaSonID);
                if (!string.IsNullOrWhiteSpace(SeriesID))
                {
                    whereSql += string.Format(@" AND OHMA.SeriesID = '{0}' ", SeriesID);
                }
                if (!string.IsNullOrWhiteSpace(MaterialProperty))
                {
                    whereSql += string.Format(@" AND OHMA.MaterialProperty = '{0}' ", MaterialProperty);
                }
                if (!string.IsNullOrWhiteSpace(ItemID))
                {
                    whereSql += string.Format(@" AND OHMA.ItemID = '{0}' ", ItemID);
                }

                var countSql = string.Format(@"SELECT COUNT(1) FROM ngoods.ngoods_Order_TheMatPur AS OHMA WHERE 1=1 {0}", whereSql);
                var countCmd = Helper.GetSqlStringCommand(countSql);
                var count = Convert.ToInt32(Helper.ExecuteScalar(countCmd));
                var sql = string.Format(@"SELECT * FROM (
	                SELECT 
		                ROW_NUMBER()OVER(ORDER BY OHMA.LifeSaleRate DESC ) AS Num,
		                OHMA.MaterialID AS MinImage,
		                PMCS.ConName AS SerialName,
		                MI.ItemName,
		                OHMA.MaterialID,
		                OHMA.MaterialShortName,
                        OHMA.ColorName,
		                OHMA.RetailPrice,
		                OHMA.PurQty,
		                OHMA.SaleDate,
		                OHMA.SaleQty,
		                ROUND(OHMA.Discount,2) AS Discount,
		                ROUND(OHMA.LifeSaleRate,2) AS LifeSaleRate
	                FROM ngoods.ngoods_Order_TheMatPur AS OHMA
	                LEFT JOIN ngoods.ngoods_PL_PlanMatConSet AS PMCS
	                ON OHMA.SeriesID=PMCS.ConID AND OHMA.CompanyID=PMCS.CompanyID AND PMCS.MatConFlag = 2 
	                LEFT JOIN ngoods.ngoods_PL_ModelItem AS MI
	                ON OHMA.ItemID=MI.ItemID AND OHMA.ExtrSource=MI.ExtrSource 
	                WHERE 1=1  
	                {0}
                ) table1 WHERE Num BETWEEN ({1}-1)*{2}+1 AND {3}*{4}", whereSql, Page, Size, Page, Size);

                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var result = PaperData<OrderMaster.SeasonOrdersData>.Create(count, Size, Page);
                var list = ModelConvertHelper<OrderMaster.SeasonOrdersData>.ConvertToModel(dt);
                var listItems = list.Select(o => new OrderMaster.SeasonOrdersData
                {
                    MinImage = Request.GetOSSImageUrl(o.MinImage, "", 2),
                    SerialName = o.SerialName,
                    ItemName = o.ItemName,
                    MaterialID = o.MaterialID,
                    MaterialShortName = o.MaterialShortName,
                    ColorName = o.ColorName,
                    RetailPrice = o.RetailPrice,
                    PurQty = o.PurQty,
                    SaleDate = o.SaleDate,
                    SaleQty = o.SaleQty,
                    Discount = o.Discount,
                    LifeSaleRate = o.LifeSaleRate
                }).ToList();
                result.Items = listItems;

                return Success(result);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region 方法
        /// <summary>
        /// 获取常用词条
        /// </summary>
        /// <param name="MasterID">订货会ID</param>
        /// <param name="SerialNo">流水号</param>
        /// <returns></returns>
        public string GetRemark(int MasterID, string SerialNo)
        {
            var dc = new km_platformEntities();
            var query = dc.t_order_GradeResult_Mater.Where(t => (t.MasterID == MasterID && t.SerialNo == SerialNo && t.UserID == this.CusID && t.CompanyID == CompanyID)).FirstOrDefault();
            if (query != null)
            {
                return query.Remark;
            }
            return "";
        }

        /// <summary>
        /// 获取商品评分和常用词条名称
        /// </summary>
        /// <returns></returns>
        public OrderMaster.CommodityGradeWordList GradeWordItem(string Grade, string Word, string Score)
        {
            try
            {
                var dc = new km_platformEntities();
                var result = new OrderMaster.CommodityGradeWordList();

                var query1 = from o in dc.sys_dictionary
                             where o.dictValue == Grade && o.isRemove == false
                             select o;

                var query2 = from o in dc.sys_dictionary
                             where o.dictValue == Word && o.isRemove == false
                             select o;

                var query3 = from o in dc.sys_dictionary
                             where o.dictValue == Score && o.isRemove == false
                             select o;

                var GradeNameItem = (from o in dc.sys_dictionary
                                     join d in query1 on o.ParentId equals d.ID
                                     where o.isRemove == false
                                     select new OrderMaster.GradeWordName
                                     {
                                         Name = o.dictName
                                     }).ToList();
                result.GradeName = GradeNameItem;

                var WordNameItem = (from o in dc.sys_dictionary
                                    join d in query2 on o.ParentId equals d.ID
                                    where o.isRemove == false
                                    select new OrderMaster.GradeWordName
                                    {
                                        Name = o.dictName
                                    }).ToList();
                result.WordName = WordNameItem;

                var ScoreNameItem = (from o in dc.sys_dictionary
                    join d in query3 on o.ParentId equals d.ID
                    where o.isRemove == false
                    select new OrderMaster.GradeWordName
                    {
                        Name = o.dictName
                    }).OrderBy(d => d.Name).ToList();
                result.ScoreName = ScoreNameItem;

                return result;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// 获取商品评分数据
        /// </summary>
        /// <param name="MasterID"></param>
        /// <param name="SerialNo"></param>
        /// <returns></returns>
        public OrderMaster.CommodityScoreNum GetCommodityScore(int MasterID, string SerialNo)
        {
            try
            {
                var dc = new km_platformEntities();
                var query1 = (from o in dc.t_order_GradeResult_Mater
                              join d in dc.t_order_GradeResult_Detail
                              on o.ResultID equals d.ResultID
                              where o.MasterID == MasterID && o.SerialNo == SerialNo && o.UserID == this.CusID && o.CompanyID == this.CompanyID
                              select new OrderMaster.CommodityScoreNum
                              {
                                  ResultID = o.ResultID,
                                  AStoreRating = d.AStoreRating,
                                  BStoreRating = d.BStoreRating,
                                  StyleRating = d.StyleRating
                              }).FirstOrDefault();
                return query1;
            }
            catch (Exception ex)
            {
                var result = new OrderMaster.CommodityScoreNum();
                result.ResultID = 0;
                result.AStoreRating = null;
                result.BStoreRating = null;
                result.StyleRating = null;
                return result;
            }
        }

        //获取评分相关属性
        public OrderMaster.OrderGradeDetailDataList GetGrade(int MasterID, string SerialNo)
        {
            try
            {
                var dc = new km_platformEntities();
                var result = new OrderMaster.OrderGradeDetailDataList();

                //采用查询一次取数据
                var query1 = from o in dc.t_order_GradeResult_Mater
                             join d in dc.t_order_GradeResult_Detail
                             on o.ResultID equals d.ResultID
                             //into od
                             //from odi in od.DefaultIfEmpty()
                             where o.MasterID == MasterID && o.SerialNo == SerialNo && o.UserID == this.CusID && o.CompanyID == this.CompanyID
                             select new
                             {
                                 ResultID = o.ResultID,
                                 PersonalScore = o.PersonalScore,
                                 GradeID = d.GradeID,
                                 Score = d.Score
                             };

                var query2 = (from a in dc.t_order_GradeDetail
                              join b in query1 on a.GradeID equals b.GradeID into c
                              from d in c.DefaultIfEmpty()
                              where a.MasterID == MasterID && a.CompanyID == this.CompanyID && a.GradeRate > 0
                              select new
                              {
                                  ResultID = d == null ? 0 : d.ResultID,
                                  PersonalScore = d == null ? 0 : d.PersonalScore,
                                  GradeID = a.GradeID,
                                  GradeName = a.GradeName,
                                  GradeRate = a.GradeRate,
                                  Score = d == null ? 0 : d.Score
                              }).ToList();

                if (query2.Count > 0)
                {
                    result.ResultID = query2[0].ResultID;
                    result.PersonalScore = query2[0].PersonalScore;
                    result.ScoreGrade = new List<OrderMaster.OrderGradeDetailData>();
                    foreach (var item in query2)
                    {
                        var model = new OrderMaster.OrderGradeDetailData();
                        model.GradeID = item.GradeID;
                        model.GradeName = item.GradeName;
                        model.GradeRate = item.GradeRate;
                        model.Score = item.Score ?? 0;
                        result.ScoreGrade.Add(model);
                    }
                }
                else
                {
                    return null;
                }

                return result;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        //获取统计相关属性
        public OrderMaster.CountData GetCountNum(int MasterID, string SerialName, string ItemName, string SerialNo)
        {
            var model = new OrderMaster.CountData();
            try
            {
                var dc = new km_platformEntities();
                var query1 = (from o in dc.t_order_styledetail where o.MasterID == MasterID && o.CompanyID == this.CompanyID select new { MasterID = o.MasterID }).ToList();
                model.MasterIDSum = query1 == null ? 0 : query1.Count;
                var query2 = (from o in dc.t_order_styledetail
                              where o.MasterID == MasterID && o.SerialName == SerialName && o.ItemName == ItemName && o.CompanyID == this.CompanyID
                              select new
                              {
                                  MasterID = o.MasterID,
                                  SerialName = o.SerialName,
                                  ItemName = o.ItemName
                              }).ToList();
                model.SerialItemSum = query2 == null ? 0 : query2.Count;
                //var query3 = dc.t_order_SKCRequire.Where(t => (t.MasterID == MasterID && t.SerialName == SerialName && t.ItemName == ItemName && t.CompanyID == this.CompanyID)).FirstOrDefault();
                var query3 = dc.t_order_SKCRequire.Where(t => (t.MasterID == MasterID && t.SerialName == SerialName && t.ItemName == ItemName && t.CompanyID == this.CompanyID)).ToList();
                model.SerialItemSKC = query3 == null ? 0 : query3.Sum(d => d.SKCQty);
                var query4 = (from o in dc.t_order_styledetail
                              join d in dc.t_order_GradeResult_Mater
                              on o.SerialNo equals d.SerialNo
                              where o.MasterID == MasterID && o.SerialName == SerialName && o.ItemName == ItemName && d.MasterID == MasterID && d.UserID == this.CusID && o.CompanyID == this.CompanyID && d.CompanyID == this.CompanyID
                              select new
                              {
                                  MasterID = o.MasterID
                              }).ToList();
                model.IScoreSum = query4 == null ? 0 : query4.Count;
                var query5 = dc.t_order_GradeResult_Mater.Where(t => (t.MasterID == MasterID && t.SerialNo == SerialNo && t.CompanyID == this.CompanyID)).ToList();
                model.SerialNoSum = query5 == null ? 0 : query5.Count;

                return model;
            }
            catch (Exception ex)
            {
                model.MasterIDSum = 0;
                model.SerialItemSum = 0;
                model.SerialItemSKC = 0;
                model.IScoreSum = 0;
                model.SerialNoSum = 0;
            }
            return model;
        }

        #endregion
        
        //[MustLogin]
        //[Description("获取已评分数据")]
        //[ReturnType(typeof(PaperData<OrderMaster.GetOrderGradeResultList>))]
        //public ApiResult GetGrade([ParamDescription("订货会内码ID")]int MasterID, [ParamDescription("流水号")]string SerialNo)
        //{
        //    try
        //    {
        //        var dc = new km_platformEntities();
        //        var result = new OrderMaster.GetOrderGradeResultList();
        //        var query = dc.t_order_GradeResult_Mater.Where(t => (t.MasterID == MasterID && t.SerialNo == SerialNo && t.UserID == this.CusID && t.CompanyID == this.CompanyID)).FirstOrDefault();
        //        if (query != null)
        //        {
        //            result.ResultID = query.ResultID;
        //            result.PersonalScore = query.PersonalScore;
        //            var queryData = dc.t_order_GradeResult_Detail.Where(t => t.ResultID == query.ResultID);
        //            result.ScoreData = queryData.Select(o => new OrderMaster.OrderGradeResultDetailData
        //            {
        //                GradeID = o.GradeID,
        //                Score = o.Score
        //            }).ToList();
        //        }
        //        return Success(result);
        //    }
        //    catch (Exception ex)
        //    {
        //        return Error(ex);
        //    }
        //}
        
    }
}
